package com.example.repository;

import com.example.model.AuditLog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 审计日志Repository
 */
@Repository
public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {

    /**
     * 根据用户ID查询审计日志
     */
    Page<AuditLog> findByUserIdOrderByOperationTimeDesc(String userId, Pageable pageable);

    /**
     * 根据操作类型查询审计日志
     */
    Page<AuditLog> findByOperationTypeOrderByOperationTimeDesc(AuditLog.OperationType operationType, Pageable pageable);

    /**
     * 根据模块查询审计日志
     */
    Page<AuditLog> findByModuleOrderByOperationTimeDesc(String module, Pageable pageable);

    /**
     * 根据状态查询审计日志
     */
    List<AuditLog> findByStatusAndOperationTimeBetween(AuditLog.OperationStatus status, 
                                                      LocalDateTime startTime, 
                                                      LocalDateTime endTime);

    /**
     * 根据风险级别查询审计日志
     */
    List<AuditLog> findByRiskLevelInAndOperationTimeBetween(List<AuditLog.RiskLevel> riskLevels,
                                                           LocalDateTime startTime,
                                                           LocalDateTime endTime);

    /**
     * 根据用户ID和时间范围查询
     */
    Page<AuditLog> findByUserIdAndOperationTimeBetween(String userId, 
                                                      LocalDateTime startTime, 
                                                      LocalDateTime endTime, 
                                                      Pageable pageable);

    /**
     * 根据操作类型和时间范围查询
     */
    Page<AuditLog> findByOperationTypeAndOperationTimeBetween(AuditLog.OperationType operationType,
                                                             LocalDateTime startTime,
                                                             LocalDateTime endTime,
                                                             Pageable pageable);

    /**
     * 根据用户ID、操作类型和时间范围查询
     */
    Page<AuditLog> findByUserIdAndOperationTypeAndOperationTimeBetween(String userId,
                                                                      AuditLog.OperationType operationType,
                                                                      LocalDateTime startTime,
                                                                      LocalDateTime endTime,
                                                                      Pageable pageable);

    /**
     * 根据时间范围查询
     */
    Page<AuditLog> findByOperationTimeBetween(LocalDateTime startTime, 
                                             LocalDateTime endTime, 
                                             Pageable pageable);

    /**
     * 根据客户端IP查询
     */
    List<AuditLog> findByClientIpAndOperationTimeBetween(String clientIp,
                                                        LocalDateTime startTime,
                                                        LocalDateTime endTime);

    /**
     * 查询失败的登录尝试
     */
    @Query("SELECT al FROM AuditLog al WHERE al.operationType = 'LOGIN' AND al.status = 'FAILED' " +
           "AND al.clientIp = :clientIp AND al.operationTime >= :startTime")
    List<AuditLog> findFailedLoginAttempts(@Param("clientIp") String clientIp, 
                                          @Param("startTime") LocalDateTime startTime);

    /**
     * 统计操作类型数量
     */
    @Query("SELECT al.operationType, COUNT(al) FROM AuditLog al " +
           "WHERE al.operationTime BETWEEN :startTime AND :endTime " +
           "GROUP BY al.operationType")
    List<Object[]> countByOperationTypeAndOperationTimeBetween(@Param("startTime") LocalDateTime startTime,
                                                              @Param("endTime") LocalDateTime endTime);

    /**
     * 统计用户操作数量
     */
    @Query("SELECT al.userId, COUNT(al) FROM AuditLog al " +
           "WHERE al.operationTime BETWEEN :startTime AND :endTime " +
           "GROUP BY al.userId ORDER BY COUNT(al) DESC")
    List<Object[]> countByUserIdAndOperationTimeBetween(@Param("startTime") LocalDateTime startTime,
                                                       @Param("endTime") LocalDateTime endTime);

    /**
     * 统计IP操作数量
     */
    @Query("SELECT al.clientIp, COUNT(al) FROM AuditLog al " +
           "WHERE al.operationTime BETWEEN :startTime AND :endTime " +
           "GROUP BY al.clientIp ORDER BY COUNT(al) DESC")
    List<Object[]> countByClientIpAndOperationTimeBetween(@Param("startTime") LocalDateTime startTime,
                                                         @Param("endTime") LocalDateTime endTime);

    /**
     * 查询异常操作
     */
    @Query("SELECT al FROM AuditLog al WHERE " +
           "(al.status = 'FAILED' OR al.riskLevel IN ('HIGH', 'CRITICAL')) " +
           "AND al.operationTime BETWEEN :startTime AND :endTime " +
           "ORDER BY al.operationTime DESC")
    List<AuditLog> findAbnormalOperations(@Param("startTime") LocalDateTime startTime,
                                         @Param("endTime") LocalDateTime endTime);

    /**
     * 查询频繁操作的用户
     */
    @Query("SELECT al.userId, COUNT(al) as operationCount FROM AuditLog al " +
           "WHERE al.operationTime >= :startTime " +
           "GROUP BY al.userId " +
           "HAVING COUNT(al) > :threshold " +
           "ORDER BY COUNT(al) DESC")
    List<Object[]> findFrequentUsers(@Param("startTime") LocalDateTime startTime,
                                    @Param("threshold") long threshold);

    /**
     * 查询频繁操作的IP
     */
    @Query("SELECT al.clientIp, COUNT(al) as operationCount FROM AuditLog al " +
           "WHERE al.operationTime >= :startTime " +
           "GROUP BY al.clientIp " +
           "HAVING COUNT(al) > :threshold " +
           "ORDER BY COUNT(al) DESC")
    List<Object[]> findFrequentIps(@Param("startTime") LocalDateTime startTime,
                                  @Param("threshold") long threshold);

    /**
     * 删除过期日志
     */
    @Modifying
    @Query("DELETE FROM AuditLog al WHERE al.operationTime < :cutoffTime")
    int deleteByOperationTimeBefore(@Param("cutoffTime") LocalDateTime cutoffTime);

    /**
     * 统计总操作数量
     */
    @Query("SELECT COUNT(al) FROM AuditLog al WHERE al.operationTime BETWEEN :startTime AND :endTime")
    long countByOperationTimeBetween(@Param("startTime") LocalDateTime startTime,
                                    @Param("endTime") LocalDateTime endTime);

    /**
     * 统计成功操作数量
     */
    @Query("SELECT COUNT(al) FROM AuditLog al WHERE al.status = 'SUCCESS' " +
           "AND al.operationTime BETWEEN :startTime AND :endTime")
    long countSuccessOperations(@Param("startTime") LocalDateTime startTime,
                               @Param("endTime") LocalDateTime endTime);

    /**
     * 统计失败操作数量
     */
    @Query("SELECT COUNT(al) FROM AuditLog al WHERE al.status = 'FAILED' " +
           "AND al.operationTime BETWEEN :startTime AND :endTime")
    long countFailedOperations(@Param("startTime") LocalDateTime startTime,
                              @Param("endTime") LocalDateTime endTime);

    /**
     * 查询平均执行时间
     */
    @Query("SELECT AVG(al.executionTime) FROM AuditLog al " +
           "WHERE al.executionTime IS NOT NULL " +
           "AND al.operationTime BETWEEN :startTime AND :endTime")
    Double getAverageExecutionTime(@Param("startTime") LocalDateTime startTime,
                                  @Param("endTime") LocalDateTime endTime);

    /**
     * 查询最慢的操作
     */
    @Query("SELECT al FROM AuditLog al " +
           "WHERE al.executionTime IS NOT NULL " +
           "AND al.operationTime BETWEEN :startTime AND :endTime " +
           "ORDER BY al.executionTime DESC")
    List<AuditLog> findSlowestOperations(@Param("startTime") LocalDateTime startTime,
                                        @Param("endTime") LocalDateTime endTime,
                                        Pageable pageable);

    /**
     * 查询用户的最近操作
     */
    @Query("SELECT al FROM AuditLog al WHERE al.userId = :userId " +
           "ORDER BY al.operationTime DESC")
    List<AuditLog> findRecentOperationsByUser(@Param("userId") String userId, Pageable pageable);

    /**
     * 搜索日志
     */
    @Query("SELECT al FROM AuditLog al WHERE " +
           "(LOWER(al.operationDesc) LIKE LOWER(CONCAT('%', :keyword, '%')) " +
           "OR LOWER(al.module) LIKE LOWER(CONCAT('%', :keyword, '%')) " +
           "OR LOWER(al.username) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
           "AND al.operationTime BETWEEN :startTime AND :endTime " +
           "ORDER BY al.operationTime DESC")
    Page<AuditLog> searchLogs(@Param("keyword") String keyword,
                             @Param("startTime") LocalDateTime startTime,
                             @Param("endTime") LocalDateTime endTime,
                             Pageable pageable);
}
